package com.fc.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import com.fc.db.DBConnect;
import com.fc.model.Item;
import com.fc.model.Worker;
import com.fc.util.Pagination;

public class ItemControl {

	public Pagination pageInstance = null;
	
	//add item
	public static boolean addItem(Item item){
		DBConnect dbc = new DBConnect();
		String sql = "insert into item (store_id,item_name) value(?,?)";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1, item.getStore_id());
			dbc.setString(2, item.getItem_name());
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	//delete item
	public static boolean deleteItem(int id){
		DBConnect dbc = new DBConnect();
		String sql = "delete from item where id=?";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1, id);
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	//update item
	public static boolean updateItem(Item item,int id){
		DBConnect dbc = new DBConnect();
		String sql = "update item set total_use=?,item_name=? where id=?";
		dbc.prepareStatement(sql);
		try{	
			dbc.setInt(1,item.getTotal_use());
			dbc.setString(2, item.getItem_name());
			dbc.setInt(3,id);
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	public static Item getItemInfo(int id){
		DBConnect dbc = new DBConnect();
		String sql = "select * from item where id=?";
		dbc.prepareStatement(sql);
		try{	
			Item item = new Item();
			dbc.setInt(1,id);
			ResultSet rs = dbc.executeQuery();
			if(rs.next())
			{
			  item.setStore_id(rs.getInt("store_id"));
			  item.setTotal_use(rs.getInt("total_use"));
			  item.setItem_name(rs.getString("item_name"));
			   return item;
			}
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			dbc.close();
		}
		return null;
	}
	public Collection getItem(int page,int rowsPerPage)throws SQLException,Exception{
		String sql = "select * from item";
		pageInstance = new ItemControlPage(sql,rowsPerPage);
		try{
			return pageInstance.getPage(page);
		}catch(SQLException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
}

class ItemControlPage extends Pagination{
	public ItemControlPage(String sql,int rowsPerPage){
		super(sql,rowsPerPage,1);
	}
	@Override
	protected Collection<Item> packResultSet(ResultSet rs) throws SQLException {
		Collection<Item> itemList = new ArrayList<Item>();
		while (rs.next()) {
			Item item=new Item();
			item.setId(rs.getInt("id"));
			item.setStore_id(rs.getInt("store_id"));
			item.setTotal_use(rs.getInt("total_use"));
			item.setItem_name(rs.getString("item_name"));
			itemList.add(item);
			item = null;
		}
		return itemList;
	}
	
}
